﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_personal_month_stats')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_personal_month_stats

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-31 08:44:41
-- Database:    [scm_main-hysj2]
-- Description: 个人中心-月度考勤统计
-- =============================================

CREATE PROCEDURE bd_proc_personal_month_stats
  @userSerial BIGINT,
  @queryDate  DATE
AS
BEGIN
  SET NOCOUNT ON;

  CREATE TABLE #result
  (
    user_serial BIGINT,
    user_no VARCHAR(20),
    user_name VARCHAR(100),
    dep_id BIGINT,
    dep_name VARCHAR(200),
    should_hour INT DEFAULT 0,    --应出工时
    actual_hour INT DEFAULT 0,    --实出工时
    leave_hour  INT DEFAULT 0,    --请假

    overtime_hour INT DEFAULT 0,  --加班
    overtime_ordinary_hour INT DEFAULT 0, --平时加班
    overtime_weekend_hour INT DEFAULT 0,    --双休加班
    overtime_statutory_hour INT DEFAULT 0,  --节假日加班

    owe_hour  INT DEFAULT 0,      --欠工
    late_count INT DEFAULT 0,      --迟到
    early_count  INT DEFAULT 0,    --早退
    absent_count INT DEFAULT 0,    --缺勤


    should_hour_name DECIMAL(8,2) DEFAULT 0,    --应出工时小时
    actual_hour_name DECIMAL(8,2) DEFAULT 0,    --实出工时小时
    leave_hour_name  DECIMAL(8,2) DEFAULT 0,    --请假小时

    overtime_hour_name DECIMAL(8,2) DEFAULT 0,  --加班小时
    overtime_ordinary_hour_name DECIMAL(8,2) DEFAULT 0, --平时加班小时
    overtime_weekend_hour_name DECIMAL(8,2) DEFAULT 0,    --双休加班小时
    overtime_statutory_hour_name DECIMAL(8,2) DEFAULT 0,  --节假日加班小时

    owe_hour_name  DECIMAL(8,2) DEFAULT 0,      --欠工
    leave_detail  VARCHAR(MAX)    --请假明细json
  )

  INSERT INTO #result(user_serial, user_no, user_name, dep_id, dep_name)
  SELECT user_serial, user_no, user_lname, user_dep, dep_name
  FROM bd_view_user_all
  WHERE user_serial = @userSerial

  DECLARE @settleYear INT, @settleMonth INT

  SELECT @settleYear = settleYear, @settleMonth = settleMonth FROM dbo.bd_fn_getSettle(@queryDate)  


  UPDATE r
  SET should_hour = x.should_hours
     ,actual_hour = x.actual_hours
     ,leave_hour = x.leave_hours_total
     ,overtime_hour = x.ot_pending_hours
     ,overtime_ordinary_hour = x.ot_normal_hours
     ,overtime_weekend_hour = x.ot_weekend_hours
     ,overtime_statutory_hour = x.ot_holiday_hours
  FROM #result r
  INNER JOIN (
      SELECT
        user_serial, SUM(should_hours) should_hours, SUM(actual_hours) actual_hours, SUM(leave_hours_total) leave_hours_total, SUM(ot_pending_hours) ot_pending_hours, SUM(ot_normal_hours) ot_normal_hours, SUM(ot_weekend_hours) ot_weekend_hours, SUM(ot_holiday_hours) ot_holiday_hours
      FROM bd_kq_timecard
      WHERE user_serial = @userSerial AND settle_year = @settleYear AND settle_month = @settleMonth
      GROUP BY user_serial
    ) x ON x.user_serial = r.user_serial


  UPDATE r
  SET late_count = x.late_count
     ,early_count = x.early_count
     ,absent_count = x.absent_count
  FROM #result r
  INNER JOIN (
      SELECT
        user_serial, SUM(CASE WHEN status = 4 THEN 1 ELSE 0 END) late_count, SUM(CASE WHEN status = 5 THEN 1 ELSE 0 END) early_count, SUM(CASE WHEN status = 6 THEN 1 ELSE 0 END) absent_count
      FROM bd_kq_timecard
      WHERE user_serial = @userSerial AND settle_year = @settleYear AND settle_month = @settleMonth
      GROUP BY user_serial
    ) x ON x.user_serial = r.user_serial

  --欠工
  UPDATE r
  SET owe_hour = x.owe
  FROM #result r
  INNER JOIN (
      SELECT
        user_serial, owe
      FROM bd_view_kq_holidays_time_rowno
      WHERE user_serial = @userSerial AND row_no = 1
    ) x ON x.user_serial = r.user_serial

  DECLARE @leave TABLE
  (
  	user_serial BIGINT,
  	leave_id VARCHAR(20),
    leave_type_name VARCHAR(50),
    leave_min INT DEFAULT 0,
    leave_hour  DECIMAL(8,2)  DEFAULT 0
  )
  
  INSERT INTO @leave (user_serial, leave_id, leave_type_name, leave_min)
  SELECT user_serial, leave_id, leave_type_name, SUM(jsjg) leave_total
  FROM bd_view_leave
  WHERE user_serial = @userSerial AND settle_year = @settleYear AND settle_month = @settleMonth AND ISNULL(status, 0) BETWEEN 3 AND 4
  GROUP BY user_serial, leave_id, leave_type_name

  UPDATE @leave SET leave_hour = CAST(leave_min / 60.0 AS DECIMAL(8,2))

  DECLARE @json VARCHAR(MAX) = ''
  SET @json = (SELECT user_serial, leave_id, leave_type_name, leave_hour FROM @leave FOR JSON AUTO)

  UPDATE #result SET leave_detail = @json

  UPDATE #result
  SET should_hour_name = CAST(should_hour / 60.0 AS DECIMAL(8, 2))
     ,actual_hour_name = CAST(actual_hour / 60.0 AS DECIMAL(8, 2))
     ,leave_hour_name = CAST(leave_hour / 60.0 AS DECIMAL(8, 2))
     ,overtime_hour_name = CAST(overtime_hour / 60.0 AS DECIMAL(8, 2))
     ,overtime_ordinary_hour_name = CAST(overtime_ordinary_hour / 60.0 AS DECIMAL(8, 2))
     ,overtime_weekend_hour_name = CAST(overtime_weekend_hour / 60.0 AS DECIMAL(8, 2))
     ,overtime_statutory_hour_name = CAST(overtime_statutory_hour / 60.0 AS DECIMAL(8, 2))
     ,owe_hour_name = CAST(owe_hour / 60.0 AS DECIMAL(8, 2))
 

  SELECT * FROM #result

  DROP TABLE #result

  SET NOCOUNT OFF;
END